﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BE;
using System.Data.SqlClient;

namespace DAL
{
	public class VertexDA
	{
		//Define Parameters to use in objectCommand objects
		private const string ParamId = "@id";
		private const string ParamName = "@name";

		//define name of Database values
		private const string Table = "Locations";
		private const string ColId = "ID";
		private const string ColName = "Name";
		private const string space = ", ";

		private SqlConnection con;

		public VertexDA()
		{
			SqlCommands cmd = new SqlCommands();
			con = cmd.GetConnection();
		}

		private Vertex extractVertex( SqlDataReader reader )
		{
			return new Vertex( reader.GetInt32( reader.GetOrdinal( ColId ) ),
				reader.GetString( reader.GetOrdinal( ColName ) ) );

		}

		private void setCommand( SqlCommand cmd, Vertex vertex )
		{
			cmd.Parameters.AddWithValue( ParamName, vertex.Name );
		}

		public int AddVertex( Vertex vertex )
		{
			string insertString = "INSERT INTO " + Table + "(" + ColName + ") VALUES(" + ParamName + ")";
			SqlCommand cmd = new SqlCommand( insertString, con );
			setCommand( cmd, vertex );
			try
			{
				con.Open();
				return cmd.ExecuteNonQuery();
			}
			catch
			{
				throw new EasyException( "There was something wrong with the Database Connection" );
			}
			finally
			{
				con.Close();
			}
		}

		public Vertex GetVertex( int id )
		{
			string sqlString = "SELECT * FROM " + Table + " WHERE " + ColId + " = " + id;
			SqlCommand cmd = new SqlCommand( sqlString, con );
			SqlDataReader reader;
			try
			{
				con.Open();
				reader = cmd.ExecuteReader();
				reader.Read();
				Vertex v = extractVertex( reader );
				reader.Close();
				return v;
			}
			catch
			{
				throw new EasyException( "There was something wrong with the Database Connection" );
			}
			finally
			{
				con.Close();
			}
		}

		public List<Vertex> GetAllVertices()
		{
			string sqlString = "SELECT * FROM " + Table;
			SqlCommand cmd = new SqlCommand( sqlString, con );
			SqlDataReader reader;
			try
			{
				con.Open();
				reader = cmd.ExecuteReader();
				List<Vertex> list = new List<Vertex>();
				while ( reader.Read() )
				{
					list.Add( extractVertex( reader ) );
				}
				reader.Close();
				con.Close();
				return list;
			}
			catch ( Exception e )
			{
				throw e;
			}
			finally
			{
				con.Close();
			}
		}

		public int EditVertex( Vertex vertex )
		{
			string sqlString = "UPDATE " + Table + " set ";

			sqlString += ColName + "=" + ParamName;
			sqlString += " Where " + ColId + "=" + vertex.ID;

			SqlCommand cmd = new SqlCommand( sqlString, con );
			setCommand( cmd, vertex );
			try
			{
				con.Open();
				return cmd.ExecuteNonQuery();
			}
			catch
			{
				throw new EasyException( "There was something wrong with the Database Connection" );
			}
			finally
			{
				con.Close();
			}
		}

		public int DeleteVertex( int id )
		{
			string sqlString = "DELETE FROM " + Table + " WHERE " + ColId + "=" + id;


			SqlCommand cmd = new SqlCommand( sqlString, con );

			try
			{
				con.Open();
				return cmd.ExecuteNonQuery();
			}
			catch
			{
				throw new EasyException( "There was something wrong with the Database Connection" );
			}
			finally
			{
				con.Close();
			}
		}

		public SortedDictionary<int, Vertex> GetDictionariedVertices()
		{
			string sqlString = "SELECT * FROM " + Table;
			SqlCommand cmd = new SqlCommand( sqlString, con );
			SqlDataReader reader;
			try
			{
				con.Open();
				reader = cmd.ExecuteReader();
				SortedDictionary<int,Vertex> list = new SortedDictionary<int, Vertex>();
				while ( reader.Read() )
				{
					Vertex v =  extractVertex(reader);
					list.Add(v.ID,v);
				}
				reader.Close();
				con.Close();
				return list;
			}
			catch ( Exception e )
			{
				throw e;
			}
			finally
			{
				con.Close();
			}
		}
	}
}
